from datetime import datetime
from typing import Any, List, Dict
import statistics
import os
import requests
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage

from sqlalchemy import func
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt


from db.models import Product, PointsOfSale
from .utils import (
    ProductEvaluationData,
    ProductEvaluationData,
    CumulativeProductEvaluationData,
    AverageProductEvaluationData,
    PointsOfSaleStatisticsData,
    ProductWithBadReviewsData,
    StatisticsData,
    TableData,
    StatisticsFilesData,
    EmailForMallingData
)
from utils import get_list_of_links_of_prouducts_to_parsed
from db.main import session
import settings


class DataGeneration:
    """ Формируем данные """

    def categorize_products_into_groups(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> Dict[str, List[ProductEvaluationData]]:
        """ Разбиваем продукты по группам """

        grouped_products = {}

        for product in list_of_products:
            if grouped_products.get(product['name']):
                grouped_products[product['name']].append(product)
            else:
                grouped_products[product['name']] = [product]

        return grouped_products


    def get_cumulative_evaluation_of_products(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> List[CumulativeProductEvaluationData]:
        """ Получаем накопительную оценку продуктов """

        def get_cumulative_evaluation(product) -> float:
            """ Добавляем накопительную оценку """

            cumulative_evaluation = 0.0

            for product in list_of_products:
                if isinstance(product['evaluation'], str): continue
                cumulative_evaluation += product['evaluation']

            return round(cumulative_evaluation, 2)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            cumulative_product_evaluation = CumulativeProductEvaluationData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_cumulative_evaluation(list_of_products)
            )
            result.append(cumulative_product_evaluation)

        return result


    def get_avarage_product_evaluation(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> List[AverageProductEvaluationData]:
        """ Получаем среднюю оценку продуктов """

        def get_average_evaluation(list_of_products) -> float:
            """ Получаем среднюю оценку """

            product_evaluations = []
            for product in list_of_products:
                if isinstance(product['evaluation'], str): continue
                product_evaluations.append(product['evaluation'])

            return round(statistics.mean(product_evaluations), 2)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            average_product_evaluation = AverageProductEvaluationData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_average_evaluation(list_of_products)
            )
            result.append(average_product_evaluation)

        return result


    def get_points_of_sale_statistics(
        self, 
        list_of_products: List[ProductEvaluationData]
    ) -> List[PointsOfSaleStatisticsData]:
        """ Получаем продажи продукта """

        def get_points_of_sale_of_product(list_of_products) -> Any:
            """ Получаем точки продаж продукта """

            nonlocal total_number_of_points_of_sale
            points_of_sale_of_product = 0

            for product in list_of_products:
                if isinstance(product['number_of_points_of_sale'], str): continue
                total_number_of_points_of_sale += product['number_of_points_of_sale']
                points_of_sale_of_product += product['number_of_points_of_sale']

            return points_of_sale_of_product


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        total_number_of_points_of_sale = 0
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            points_of_sale_statistics = PointsOfSaleStatisticsData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_points_of_sale_of_product(list_of_products),
                total_number_of_points_of_sale=total_number_of_points_of_sale
            )
            result.append(points_of_sale_statistics)

        return result


    def get_products_with_bad_review(self) -> List[ProductWithBadReviewsData]:
        """ Получаем продукты с 'плохими' отзывами """

        products_with_bad_review = []
        list_of_links_of_prouducts = get_list_of_links_of_prouducts_to_parsed()

        list_of_products = session.query(
            Product
        ).filter(
            Product.link.in_(list_of_links_of_prouducts),
            Product.raiting<4
        ).all()

        for product in list_of_products:
            product_reviews = [review.description
                               for review in product.reviews.all()]
            product_reviews = '\n\n\n'.join(product_reviews)
            product_with_bad_reviews = ProductWithBadReviewsData(
                product_name=product.name,
                date=product.date,
                list_of_reviews=product_reviews
            )
            products_with_bad_review.append(product_with_bad_reviews)

        sorted_products_with_bad_review = sorted(
            products_with_bad_review,
            key=lambda product: product['date']
        )
        return sorted_products_with_bad_review


    def get_specified_products(self) -> List[ProductEvaluationData]:
        """ Получаем список указанных продуктов """

        def get_products_from_database() -> List[Product]:
            """ Получаем продукты из базы данных """

            list_of_links_of_prouducts = get_list_of_links_of_prouducts_to_parsed()
            query = session.query(
                PointsOfSale.product_id,
                func.count(PointsOfSale.product_id).label('count')
            ).group_by(
                PointsOfSale.product_id
            ).subquery()

            list_of_products = session.query(
                Product, query.c.count
            ).join(
                query,
                query.c.product_id == Product.id,
            ).filter(
                Product.link.in_(list_of_links_of_prouducts)
            ).all()
            return list_of_products

        def get_list_of_product_evaluation_data(
            list_of_products: List[Product]
        ) -> List[ProductEvaluationData]:
            """ Получаем список объкетов ProductEvaluationData """

            list_of_product_evaluation = []

            for product in list_of_products:
                product, number_of_points_of_sale = product
                product_evaluation_data = ProductEvaluationData(
                    date=product.date,
                    name=product.name,
                    evaluation=product.raiting,
                    number_of_points_of_sale=number_of_points_of_sale
                )
                list_of_product_evaluation.append(product_evaluation_data)

            return list_of_product_evaluation

        def add_empty_products(
            list_of_products: List[ProductEvaluationData]
        ) -> List[ProductEvaluationData]:
            """ Добавляем пустые продукты для корректного отображения таблицы """

            new_list_of_products = []
            list_of_all_dates = list(set([product['date'].strftime('%d-%m-%Y')
                                 for product in list_of_products]))

            for product in list_of_products:
                product_date = product['date'].strftime('%d-%m-%Y')
                for product_date_str in list_of_all_dates:

                    if product_date == product_date_str:
                        new_list_of_products.append(product)
                        continue

                    day, month, year = map(int, product_date_str.split('-'))
                    new_date = datetime(day=day, month=month, year=year)
                    empty_product = ProductEvaluationData(
                        name=product['name'],
                        date=new_date,
                        evaluation='-',
                        number_of_points_of_sale='-'
                    )
                    new_list_of_products.append(empty_product)

            return new_list_of_products


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        list_of_products = get_products_from_database()
        list_of_products = get_list_of_product_evaluation_data(list_of_products)
        list_of_products = add_empty_products(list_of_products)

        sorted_list_of_products = sorted(
            list_of_products,
            key=lambda product: product['date']
        )
        return sorted_list_of_products


    def get_statistics_data(self) -> StatisticsData:
        """ Получаем статистику указанных продуктов """

        list_of_products = self.get_specified_products()
        cumulative_evaluation_of_products = self.get_cumulative_evaluation_of_products(
            list_of_products
        )
        avarage_product_evaluation = self.get_avarage_product_evaluation(
            list_of_products
        )
        points_of_sale_statistics = self.get_points_of_sale_statistics(
            list_of_products
        )
        products_with_bad_review = self.get_products_with_bad_review()

        statistics_data = StatisticsData(
            average_product_evaluations=avarage_product_evaluation,
            cumulative_product_evaluations=cumulative_evaluation_of_products,
            points_of_sale=points_of_sale_statistics,
            product_with_bad_reviews=products_with_bad_review
        )
        return statistics_data


class GettingStatistics(DataGeneration):
    """ Инструменты для получения статистики """

    def create_table(self, table_data: TableData, table_name: str) -> None:
        """ Создаем таблицу по соответствующим данным """

        figure, ax = plt.subplots()
        ax.axis('off')

        full_table_data = []
        full_table_data.append(table_data['header'])
        _ = [full_table_data.append(element) for element in table_data['body']]
        full_table_data.append(table_data['footer'])

        table = ax.table(
            cellText=full_table_data,
            loc='center',
            cellLoc='center'
        )
        table.auto_set_font_size(False)
        table.set_fontsize(10)
        table.scale(1.0, 5.0)

        table_cells = table.get_celld()
        for cell in table_cells:
            table_cells[cell].set_height(0.05)
            table_cells[cell].set_width(0.3)

            if not cell[0]: # Если это первая строка
                table_cells[cell].set_height(0.4)
                table_cells[cell].set_fontsize(8)

                cell_text = table_cells[cell].get_text().get_text()
                cell_text = cell_text.replace(' ', '\n')

                table_cells[cell].get_text().set_text(cell_text)
                table_cells[cell].set_aa('center')

        path_to_table = os.path.abspath(
            f'user_data/generated_statistics/tables/{table_name}'
        )
        plt.savefig(
            path_to_table,
            dpi=300,
            bbox_inches='tight'
        )


    def get_data_to_create_table(
            self,
            list_of_products: List[CumulativeProductEvaluationData],
            product_part_designation: str
        ) -> TableData:
        """ Получаем данные для создания таблицы """

        def add_table_header(
            table_header: List[str],
            list_of_product_evaluation
        ) -> None:
            """ Добавляем заголовок для таблицы """

            for product_evaluation in list_of_product_evaluation:
                product_name = product_evaluation['name']
                if not product_name in table_header:
                    table_header.append(product_name)


        def group_product_elements(
            list_of_products: Dict[str, List[int]],
            product_evaluation_data: ProductEvaluationData,
            product_part_designation: str
        ) -> None:
            """ Добавляем продукт в таблицу """

            product_evaluation = product_evaluation_data.get(
                product_part_designation
            )
            product_date = product_evaluation_data['date'].strftime('%d-%m-%Y')

            if not product_date in list_of_products:
                list_of_products[product_date] = [product_evaluation]
            else:
                list_of_products[product_date].append(product_evaluation)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        grouped_list_of_products = {}
        table_header = ['Дата']
        table_body = []
        table_footer = ['Итого']

        for list_of_product_data in list_of_products:
            table_footer.append(
                list_of_product_data.get('grand_total')
            )
            list_of_product_data = list_of_product_data['list_of_product_evaluation']

            add_table_header(table_header, list_of_product_data)

            for product_evaluation in list_of_product_data:
                group_product_elements(
                    grouped_list_of_products,
                    product_evaluation,
                    product_part_designation
                )

        for element in grouped_list_of_products:
            product_data = grouped_list_of_products[element]
            product_data.insert(0, element)
            table_body.append(product_data)

        table_data = TableData(
            header=table_header,
            body=table_body,
            footer=table_footer
        )

        return table_data


    def get_cumulative_evaluation_of_products_in_form_of_table(
        self,
        cumulative_product_evaluations: List[CumulativeProductEvaluationData]
    ) -> None:
        """ Получаем накопительную оценку продуктов в виде таблицы """

        table_data = self.get_data_to_create_table(
            cumulative_product_evaluations,
            product_part_designation='evaluation'
        )
        self.create_table(
            table_data,
            table_name='cumulative_evaluation.png'
        )


    def get_average_evaluation_of_products_in_form_of_table(
        self,
        average_product_evaluations: List[AverageProductEvaluationData]
    ) -> None:
        """ Получаем среднюю оценку продуктов в виде таблицы """

        table_data = self.get_data_to_create_table(
            average_product_evaluations,
            product_part_designation='evaluation'
        )
        self.create_table(
            table_data,
            table_name='average_evaluation.png'
        )


    def get_points_of_sales_of_product_in_form_of_table(
        self,
        points_of_sale: List[PointsOfSaleStatisticsData]
    ) -> None:
        """ Получаем точки продаж продукта в виде таблицы """

        def get_total_number_of_points_of_sale(number_of_points_of_sale) -> int:
            """ Получаем общее количество точек продаж """

            total_number_of_points_of_sale = 0
            for element in number_of_points_of_sale[1:]:
                if isinstance(element, str): continue
                total_number_of_points_of_sale += element

            return total_number_of_points_of_sale

        def add_body_to_table(table_data) -> None:
            """ Наполняем тело таблицы """

            for number_of_points_of_sale in table_data:
                number_of_points_of_sale.append(
                    get_total_number_of_points_of_sale(number_of_points_of_sale)
                )

        def add_additional_column(table_data: TableData) -> None:
            """
            Добавляем дополнительный столбец для отображения общего
            количества точек продаж продукта
            """

            table_data['header'].append('Итого')

            add_body_to_table(table_data['body'])

            table_data['footer'].append(
                sum(table_data['footer'][1:])
            )


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        table_data = self.get_data_to_create_table(
            points_of_sale,
            product_part_designation='number_of_points_of_sale'
        )
        add_additional_column(table_data)
        self.create_table(
            table_data,
            table_name='number_of_points_of_sale.png'
        )


    def get_html_for_table_of_bad_products(self, list_of_products: List[str]) -> str:
        """ Получаем html для отображения таблицы плохих продуктов """

        table_of_products_html = """
            <table>
                <tr>
                    <th>Товар</th>
                    <th>Дата</th>
                    <th>Отзыв</th>
                <tr>
        """
        list_of_products_html = ''

        for product in list_of_products:
            text = f"""
                <tr>
                    <td>{product[0]}</td>
                    <td>{product[1]}</td>
                    <td>{product[2]}</td>
                </tr>
            """
            list_of_products_html += text

        final_html = table_of_products_html + list_of_products_html + '</table>'
        styles_for_table = """
            <style>
                table {
                    border: 1px solid black;
                    border-collapse: collapse;
                }
                td {
                    border: 1px solid black;
                    padding: .5em;
                }
            </style>
        """
        final_html += styles_for_table
        return final_html


    def get_bad_product_reviews_in_form_table(
        self,
        product_with_bad_reviews: List[ProductWithBadReviewsData]
    ) -> None:

        full_table_data = []

        for product in product_with_bad_reviews:
            product_date = product['date'].strftime('%d-%m-%Y')
            for review in product['list_of_reviews'].split('\n\n\n'):
                full_table_data.append(
                    [product['product_name'], product_date, review]
                )

        html = self.get_html_for_table_of_bad_products(full_table_data)
        path_to_table = os.path.abspath(
            f'user_data/generated_statistics/tables/table_of_bad_reviews.html'
        )
        with open(path_to_table, 'w') as file: file.write(html)


    def create_statistics_for_specified_products(self) -> None:
        """ Создаем статистику для укзанных продуктов """

        statistics_data = self.get_statistics_data()
        self.get_cumulative_evaluation_of_products_in_form_of_table(
            statistics_data['cumulative_product_evaluations']
        )
        self.get_average_evaluation_of_products_in_form_of_table(
            statistics_data['average_product_evaluations']
        )
        self.get_points_of_sales_of_product_in_form_of_table(
            statistics_data['points_of_sale']
        )
        self.get_bad_product_reviews_in_form_table(
            statistics_data['product_with_bad_reviews']
        )


class SendingStatistics:
    """ Инструменты для отправки статистики """

    def get_statistics_files_data(self) -> StatisticsFilesData:
        """ Получаем объект с файлами статистики """

        statistics_file = {}
        path_to_file = os.path.abspath(
            'user_data/generated_statistics/tables'
        )
        files_with_statistics = [
            [
                'average_evaluation',
                'Средняя оценка продуктов'
            ],
            [
                'number_of_points_of_sale',
                'Количество точек продаж продуктов'
            ],
            [
                'cumulative_evaluation',
                'Накопительная оценка продуктов'
            ],
            [
                'table_of_bad_reviews',
                'Продукты с "плохими" отзывами'
            ]
        ]

        for element in files_with_statistics:

            file_name, annotation = element

            if file_name == 'table_of_bad_reviews':
                extension = '.html'
            else:
                extension = '.png'

            file_name = f'{file_name}{extension}'
            file_path = path_to_file + f'/{file_name}'
            statistics_file[file_name] = (file_path, annotation, file_name)

        statistics_files_data = StatisticsFilesData(**statistics_file)
        return statistics_files_data


    def send_statistics_to_telegram(
        self,
        statistics_files_data: StatisticsFilesData
    ) -> None:
        """ Отправляем статистику в телеграм """

        request = 'https://api.telegram.org/bot{}/sendDocument'.format(
            settings.TELEGRAM_BOT_TOKEN
        )

        for element in statistics_files_data.values():
            file_path, annotation, file_name = element
            data = {
                'chat_id': settings.TELEGRAM_CHAT_ID,
                'caption': annotation
            }
            with open(file_path, 'rb') as file:
                files = {'document': file}
                requests.post(request, data=data, files=files)


    def get_email_for_malling_data(self) -> EmailForMallingData:
        """ Получаем список email для рассылки """

        file_path = os.path.abspath('user_data/malling_by_email.txt')
        with open(file_path) as file:
            file_contents = file.read().strip()
            file_contents = file_contents.split(
                '-----------------------------'
            )[1].strip()
            sender, recipients = file_contents.split('\n\n')

            senders_email_name, senders_email_password = sender.split('\n')
            list_of_emails_for_malling = recipients.split('\n')
            return EmailForMallingData(
                senders_email_name=senders_email_name,
                senders_email_password=senders_email_password,
                list_of_emails_for_malling=list_of_emails_for_malling
            )



    def send_statistics_by_email(
        self,
        statistics_files_data: StatisticsFilesData
    ) -> None:
        """ Отправляем статистику на почту """

        email_for_malling_data = self.get_email_for_malling_data()
        server = smtplib.SMTP('smtp.mail.ru', 587)
        server.starttls()
        server.login(
            email_for_malling_data['senders_email_name'],
            email_for_malling_data['senders_email_password'],
        )

        for recipients in email_for_malling_data['list_of_emails_for_malling']:
            for file_path, annotation, file_name in statistics_files_data.values():

                message = MIMEMultipart()
                message.attach(MIMEText(annotation))
                message['from'] = email_for_malling_data['senders_email_name']
                message['to'] = recipients
                message['Subject'] = 'Статистика данных по парсингу vkusvill'

                if '.html' in file_name:
                    with open(file_path, encoding='utf8', errors='ignore') as file:
                        file_to_send = MIMEText(file.read())
                else:
                    with open(file_path, 'rb') as file:
                        file_to_send = MIMEImage(file.read())

                file_to_send.add_header(
                    'content-disposition',
                    'attachment',
                    filename=file_name
                )
                message.attach(file_to_send)
                server.sendmail(
                    email_for_malling_data['senders_email_name'],
                    recipients,
                    message.as_string()
                )


    def send_statistics_of_specified_products(self) -> None:
        """ Отправляем статистику """

        statistics_files_data = self.get_statistics_files_data()
        self.send_statistics_to_telegram(statistics_files_data)
        self.send_statistics_by_email(statistics_files_data)
